package Dao;

import entity.*;
import util.JDBCUtil;
import util.JdbcUtil2;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class PermissionDao {
    private static PermissionDao permissionDao;

    //查询所有
    public List<PermissionBean> all() {
        List<PermissionBean> list = null;
        String sql = "SELECT Pname,Ppath,Plevel FROM permission UNION SELECT firstChildPname,Ppath,Plevel FROM firstChild UNION SELECT secondChildPname,Ppath,Plevel FROM secondChild";
        list = JDBCUtil.list(sql, PermissionBean.class);
        return list;
    }

    public List getperFirstChild(int firstParentId) {
        Connection con = null; // 数据库连接对象
        Statement stm = null; // 执行语句对象
        ResultSet rs = null; // 结果集对象
        List list = new ArrayList();
        //连接数据库
        con = JdbcUtil2.createConn();
        try {
            //获取执行语句
            stm = con.createStatement();
            //获取执行语句
            String sqlfistChild = "select Plevel,firstChildID_Parent,firstChildPname,secondChildID from firstChild where firstChildID_Parent=" + firstParentId;
            //执行查询操作
            rs = stm.executeQuery(sqlfistChild);
            //遍历结果集
            while (rs.next()) {
                PerFirstChild perFirstChildbean = new PerFirstChild();
                perFirstChildbean.setPlevel(rs.getInt("Plevel"));
                perFirstChildbean.setFirstChildID_Parent(rs.getInt("firstChildID_Parent"));
                perFirstChildbean.setFirstChildPname(rs.getString("firstChildPname"));
                perFirstChildbean.setSecondChildID(rs.getInt("secondChildID"));
                perFirstChildbean.setSecondChild(getperSecondChild(perFirstChildbean.getSecondChildID()));
                list.add(perFirstChildbean);
            }
        } catch (SQLException e) {
            System.out.println("第一层出错");
            e.printStackTrace();
        } finally {
            //释放资源
            JdbcUtil2.close(rs, stm, con);
        }
        return list;
    }

    public List getperSecondChild(int secondParentId) {
        Connection con = null; // 数据库连接对象
        Statement stm = null; // 执行语句对象
        ResultSet rs = null; // 结果集对象
        List list = new ArrayList();
        //连接数据库
        con = JdbcUtil2.createConn();
        try {
            //获取执行语句
            stm = con.createStatement();
            //获取执行语句
            String sqlSecondChild = "select Plevel,firstChildID_Parent,secondChildPname from secondChild where firstChildID_Parent=" + secondParentId;
            //执行查询操作
            rs = stm.executeQuery(sqlSecondChild);
            //遍历结果集
            while (rs.next()) {
                PerSecondChild perSecondChild = new PerSecondChild();
                perSecondChild.setPlevel(rs.getInt("Plevel"));
                perSecondChild.setSecondChildPname(rs.getString("secondChildPname"));
                perSecondChild.setFirstChildID_Parent(rs.getInt("firstChildID_Parent"));
                list.add(perSecondChild);
            }
        } catch (
                SQLException e) {
            System.out.println("第一层出错");
            e.printStackTrace();
        } finally {
            //释放资源
            JdbcUtil2.close(rs, stm, con);
        }
        return list;
    }

    public List getPerAll() {
        Connection con = null; // 数据库连接对象
        Statement stm = null; // 执行语句对象
        ResultSet rs = null; // 结果集对象
        List list = new ArrayList();
        //获取sql语句
        String sql = "SELECT * FROM permission";
        //连接数据库
        con = JdbcUtil2.createConn();
        try {
            //获取执行语句
            stm = con.createStatement();
            //执行查询操作
            rs = stm.executeQuery(sql);
            //遍历结果集
            while (rs.next()) {
                PermissionBean permissionBean = new PermissionBean();
                permissionBean.setPid(rs.getInt("Pid"));
                permissionBean.setPname(rs.getString("Pname"));
                permissionBean.setPpath(rs.getString("Ppath"));
                permissionBean.setPlevel(rs.getInt("Plevel"));
                permissionBean.setFirstChildId(rs.getInt("firstChildId"));
                permissionBean.setFirstChild(getperFirstChild(permissionBean.getFirstChildId()));
                list.add(permissionBean);
            }
        } catch (SQLException e) {
            System.out.println("第一层出错");
            e.printStackTrace();
        } finally {
            //释放资源
            JdbcUtil2.close(rs, stm, con);
        }
        return list;
    }

    //根据id获取权限
    public PermissionBean one(int id) {
        PermissionBean PermissionBean = null;
        String sql = "select Pname,Ppath from userList,roles_per,permission where Uid = ? AND userList.UroleId=roles_per.roles_per_RId AND roles_per.roles_per_PId=permission.Pid";
        PermissionBean = JDBCUtil.one(sql, PermissionBean.class, id);
        return PermissionBean;
    }

    //添加新权限
    public int add(PermissionBean permissionBean) {
        int num = 0;
        String sql = "insert into permission (Pid,Pname,Ppath) values (?,?,?);";
        num = JDBCUtil.update(sql, permissionBean.getPid(), permissionBean.getPname(), permissionBean.getPpath());
        return num;
    }

    //根据id删除权限
    public int del(int id) {
        int num = 0;
        String sql = "delete from permission where Pid = ?";
        num = JDBCUtil.update(sql, id);
        return num;
    }
    public List getNavChild(int navId) {
        Connection con = null; // 数据库连接对象
        Statement stm = null; // 执行语句对象
        ResultSet rs = null; // 结果集对象
        List list = new ArrayList();
        //连接数据库
        con = JdbcUtil2.createConn();
        try {
            //获取执行语句
            stm = con.createStatement();
            //获取执行语句
            String sqlSecondChild = "SELECT childrenNav.ChildrenNavId,childrenNav.NavName,childrenNav.NavTitle,childrenNav.NavParentPath,childrenNav.Navpath,childrenNav.NavComponent,childrenNav.NavAllowAccess FROM childrenNav WHERE ChildrenNavId = "+navId;
            //执行查询操作
            rs = stm.executeQuery(sqlSecondChild);
            //遍历结果集
            while (rs.next()) {
                NavChildren navChildren = new NavChildren();
                navChildren.setChildrenNavId(rs.getInt("ChildrenNavId"));
                navChildren.setNavName(rs.getString("NavName"));
                navChildren.setNavTitle(rs.getString("NavTitle"));
                navChildren.setNavParentPath(rs.getString("NavParentPath"));
                navChildren.setNavpath(rs.getString("Navpath"));
                navChildren.setNavComponent(rs.getString("NavComponent"));
                list.add(navChildren);
            }
        } catch (
                SQLException e) {
            System.out.println("第一层出错");
            e.printStackTrace();
        } finally {
            //释放资源
            JdbcUtil2.close(rs, stm, con);
        }
        return list;
    }

    public List<NavData> getNavData(int id){
        Connection con = null; // 数据库连接对象
        Statement stm = null; // 执行语句对象
        ResultSet rs = null; // 结果集对象
        List list = new ArrayList();
        //获取sql语句
        String sql = "select navData.NavId,navData.NavName,navData.NavTitle,navData.NavParentPath,navData.Navpath,navData.NavComponent,navData.NavIcon,navData.Navchildren,navData.NavAllowAccess from userList,roles_per,permission,navData where Uid = "+id+" AND permission.NavId_FK=navData.NavId   AND userList.UroleId=roles_per.roles_per_RId AND roles_per.roles_per_PId=permission.Pid";
        //连接数据库
        con = JdbcUtil2.createConn();
        try {
            //获取执行语句
            stm = con.createStatement();
            //执行查询操作
            rs = stm.executeQuery(sql);
            //遍历结果集
            while (rs.next()) {
                NavData navData = new NavData();
                navData.setNavId(rs.getInt("NavId"));
                navData.setNavName(rs.getString("NavName"));
                navData.setNavTitle(rs.getString("NavTitle"));
                navData.setNavParentPath(rs.getString("NavParentPath"));
                navData.setNavpath(rs.getString("Navpath"));
                navData.setNavComponent(rs.getString("NavComponent"));
                navData.setNavIcon(rs.getString("NavIcon"));
                navData.setNavchildren(getNavChild(rs.getInt("Navchildren")));
                navData.setNavAllowAccess(rs.getBoolean("NavAllowAccess"));
                list.add(navData);
            }
        } catch (SQLException e) {
            System.out.println("第一层出错");
            e.printStackTrace();
        } finally {
            //释放资源
            JdbcUtil2.close(rs, stm, con);
        }
        return list;
    }

    public List<PermissionBean> searchbyname(String searchname){
        List<PermissionBean> list = null;
        String sql = "(SELECT Pname,Ppath,Plevel FROM permission where Pname like '%"+searchname+"%')" +
                "UNION ALL" +
                "(SELECT firstChildPname,Ppath,Plevel FROM firstChild where firstChildPname like '%"+searchname+"%')" +
                "UNION ALL" +
                "(SELECT secondChildPname,Ppath,Plevel FROM secondChild where secondChildPname like '%"+searchname+"%')";
        list = JDBCUtil.list(sql, PermissionBean.class);
        return list;
    }

    public List<PermissionBean> searchbylevelid(int searchid){
        List<PermissionBean> list = null;
        String sql = "(SELECT Pname,Ppath,Plevel FROM permission where Plevel = "+searchid+")" +
                "UNION ALL" +
                "(SELECT firstChildPname,Ppath,Plevel FROM firstChild where Plevel = "+searchid+")" +
                "UNION ALL" +
                "(SELECT secondChildPname,Ppath,Plevel FROM secondChild where Plevel = "+searchid+")";
        list = JDBCUtil.list(sql, PermissionBean.class);
        return list;
    }



    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        PermissionDao permissionDao1 = new PermissionDao();
        List<NavData> navData = permissionDao1.getNavData(1001);
        System.out.println(navData);
    }
}
